import time
import numpy
import pymysql
import pandas as pd
from datetime import datetime

from pymysql.cursors import Cursor, DictCursor

class MysqlUtils(object):
    def __init__(self, *args):
        self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            password='sjk1234',
            db='scenic',
            port=3306,
            charset='utf8'
        )
    def get_scenic_data(self):
        """获取数据
        """
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
        SELECT order_id, age, user_count, id_no, user_name, phone
        FROM (
            SELECT o.id as order_id, u.id_no, u.user_name, u.phone,
            CASE WHEN LENGTH(u.id_no) = 18 THEN YEAR(NOW()) - CAST(SUBSTR(u.id_no, 7, 4) AS SIGNED) ELSE NULL END AS age,
            (SELECT COUNT(*) FROM ticket_order_user_rel tou WHERE tou.order_id = o.id) as user_count
            FROM ticket_order o 
            JOIN ticket_order_user_rel u ON u.order_id = o.id
            WHERE u.id_no IS NOT NULL AND u.id_no != ''
        ) as subquery
        WHERE user_count = 1 AND (age < 18 OR age >= 60);
        """
        cursor.execute(sql)
        ret = cursor.fetchall()

        df = pd.DataFrame(ret)
        print(df.head)
        df.to_csv('./outliner/scenic_data.csv')


if __name__ == '__main__':
    mu = MysqlUtils()
    mu.get_scenic_data()